﻿Imports Excel
Imports System.Windows.Forms

Public Class OutYear_User
    Inherits ExcelCreateBase

    Public Event PageComplete(ByVal page As Integer, ByVal totalPage As Integer)
    Public Event PrintProcess(ByVal content As String)

    'Const pageCount = 53
    Private _bOpen As Boolean = False
    Private _bClose As Boolean = False
    Private _bInitTemp As Boolean = False

    Private _backCurrentIndex As Integer = 0
    Private _endColumnName = "D"

    Public Sub New(ByVal fileName As String)
        MyBase.New(fileName)
    End Sub

    Private Sub PageSetUp()
        With Me.GetCurrentWorkSheet.PageSetup
            .LeftMargin = CentimetersToPoints(0.5)
            .RightMargin = CentimetersToPoints(0.5)
            .TopMargin = CentimetersToPoints(1.5)
            .BottomMargin = CentimetersToPoints(0.5)
            .HeaderMargin = CentimetersToPoints(0.5)
            .FooterMargin = CentimetersToPoints(0.5)
            .CenterHorizontally = True
            .CenterVertically = False
        End With
    End Sub

    Public Sub DoOutData(ByVal dt As System.Data.DataTable, ByVal sheetIndex As Integer, ByVal AutoClose As Boolean)
        '设置当前SHEET
        CurrentSheetIndex = sheetIndex
        _backCurrentIndex = CurrentSheetIndex

        If _bOpen = False Then
            '打开Excel
            RaiseEvent PrintProcess("正在打开Excel")
            打开()
            _bOpen = True
        End If
        '设置名称
        GetCurrentWorkSheet.Select()
        GetCurrentWorkSheet.Name = dt.Rows(0)("用户")

        '进行页面设置
        RaiseEvent PrintProcess("正在页面设置")

        PageSetUp()
        '计算页数
        RaiseEvent PrintProcess("正在计算打印页数")
        Dim page As Integer = Fix(dt.Rows.Count / (报表记录条数字 - 1))
        If dt.Rows.Count Mod (报表记录条数字 - 1) <> 0 Then
            page += 1
        End If

        RaiseEvent PrintProcess("共" & page & "页")
        '设置每一页的模版
        For index As Integer = 1 To page
            '第一页 先设置好做模版
            If index = 1 AndAlso _bInitTemp = False Then
                RaiseEvent PrintProcess("正在初始化模版")
                SetTemp()
                _bInitTemp = True
            Else
                SetTemp(index)
            End If
        Next
        RaiseEvent PrintProcess("初始化模版成功")

        RaiseEvent PrintProcess("正在打印内容")
        PrintContent(dt)

        If AutoClose = True Then
            RaiseEvent PrintProcess("打印结束")
            保存()
            RaiseEvent PrintProcess("正在关闭......")
            关闭()
            _bClose = True
            RaiseEvent PrintProcess("")
        End If

    End Sub

    Public Sub DoOutDataA(ByVal dt As System.Data.DataTable, ByVal sheetIndex As Integer, ByVal AutoClose As Boolean, Optional ByVal index As Integer = 0)

        '设置当前SHEET
        CurrentSheetIndex = sheetIndex
        _backCurrentIndex = CurrentSheetIndex

        If _bOpen = False Then
            '打开Excel
            RaiseEvent PrintProcess("正在打开Excel")
            打开()
            _bOpen = True
        End If

        If index = 0 Then
            RaiseEvent PrintProcess("正在收入趋势图")
            Print收入趋势图(dt)
        ElseIf index = 1 Then
            RaiseEvent PrintProcess("正在支出趋势图")
            Print支出趋势图(dt)
        ElseIf index = 2 Then
            RaiseEvent PrintProcess("正在合计趋势图")
            Print节余趋势图(dt)
        End If

        If AutoClose = True Then
            RaiseEvent PrintProcess("打印结束")
            保存()
            RaiseEvent PrintProcess("正在关闭......")
            关闭()
            _bClose = True
            RaiseEvent PrintProcess("")
        End If

    End Sub

    Private Sub Print收入趋势图(ByVal dt As System.Data.DataTable)

        Dim 标签列 As String = "1"
        Dim 值列 As String = "2"

        Dim 开始行 As String = "2"
        Dim 结束行 As String = dt.Rows.Count

        Dim sheetName As String = ""
        Dim ws As Object
        Dim chart As Object

        chart = Me.GetCurrentWorkBook.Charts.Add(, Me.GetCurrentWorkBook.Sheets.Item(Me.GetCurrentWorkBook.Sheets.Count))
        chart.ChartType = XlChartType.xlLineMarkers

        '开始添加系列
        For index As Integer = 1 To Me.GetCurrentWorkBook.Sheets.Count
            Try

                ws = Me.GetCurrentWorkBook.Sheets.Item(index)
                sheetName = ws.Name

                chart.SeriesCollection.NewSeries()

            Catch ex As Exception

            End Try
        Next

        '开始设置
        Dim realIndex As Integer = 1

        For index As Integer = 1 To Me.GetCurrentWorkBook.Sheets.Count
            Try

                ws = Me.GetCurrentWorkBook.Sheets.Item(index)
                sheetName = ws.Name

                If index = 1 Then
                    chart.SeriesCollection(realIndex).XValues = "=" & sheetName & "!R" & 开始行 & "C" & 标签列 & ":R" & 结束行 & "C" & 标签列
                    chart.SeriesCollection(realIndex).Values = "=" & sheetName & "!R" & 开始行 & "C" & 值列 & ":R" & 结束行 & "C" & 值列
                    chart.SeriesCollection(realIndex).Name = "=""" & sheetName & """"
                Else
                    chart.SeriesCollection(realIndex).Values = "=" & sheetName & "!R" & 开始行 & "C" & 值列 & ":R" & 结束行 & "C" & 值列
                    chart.SeriesCollection(realIndex).Name = "=""" & sheetName & """"
                End If
                realIndex = realIndex + 1
            Catch ex As Exception

            End Try
        Next

        '设置图例
        chart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, False, False, False, False, False, True, False, False, False)

        chart.Location(XlChartLocation.xlLocationAsNewSheet, "收入趋势图")
        chart.Move(, Me.GetCurrentWorkBook.Sheets(Me.GetCurrentWorkBook.Sheets.Count))

        chart.HasLegend = True
        chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom

    End Sub

    Private Sub Print支出趋势图(ByVal dt As System.Data.DataTable)

        Dim 标签列 As String = "1"
        Dim 值列 As String = "3"

        Dim 开始行 As String = "2"
        Dim 结束行 As String = dt.Rows.Count

        Dim sheetName As String = ""
        Dim ws As Object
        Dim chart As Object

        chart = Me.GetCurrentWorkBook.Charts.Add(, Me.GetCurrentWorkBook.Sheets.Item(Me.GetCurrentWorkBook.Sheets.Count))
        chart.ChartType = XlChartType.xlLineMarkers

        '开始添加系列
        For index As Integer = 1 To Me.GetCurrentWorkBook.Sheets.Count - 1
            Try

                ws = Me.GetCurrentWorkBook.Sheets.Item(index)
                sheetName = ws.Name

                chart.SeriesCollection.NewSeries()

            Catch ex As Exception

            End Try
        Next

        '开始设置
        Dim realIndex As Integer = 1

        For index As Integer = 1 To Me.GetCurrentWorkBook.Sheets.Count
            Try

                ws = Me.GetCurrentWorkBook.Sheets.Item(index)
                sheetName = ws.Name

                If index = 1 Then
                    chart.SeriesCollection(realIndex).XValues = "=" & sheetName & "!R" & 开始行 & "C" & 标签列 & ":R" & 结束行 & "C" & 标签列
                    chart.SeriesCollection(realIndex).Values = "=" & sheetName & "!R" & 开始行 & "C" & 值列 & ":R" & 结束行 & "C" & 值列
                    chart.SeriesCollection(realIndex).Name = "=""" & sheetName & """"
                Else
                    chart.SeriesCollection(realIndex).Values = "=" & sheetName & "!R" & 开始行 & "C" & 值列 & ":R" & 结束行 & "C" & 值列
                    chart.SeriesCollection(realIndex).Name = "=""" & sheetName & """"
                End If
                realIndex = realIndex + 1
            Catch ex As Exception

            End Try
        Next

        '设置图例
        chart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, False, False, False, False, False, True, False, False, False)

        chart.Location(XlChartLocation.xlLocationAsNewSheet, "支出趋势图")
        chart.Move(, Me.GetCurrentWorkBook.Sheets(Me.GetCurrentWorkBook.Sheets.Count))

        chart.HasLegend = True
        chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom

    End Sub

    Private Sub Print节余趋势图(ByVal dt As System.Data.DataTable)

        Dim 标签列 As String = "1"
        Dim 值列 As String = "4"

        Dim 开始行 As String = "2"
        Dim 结束行 As String = dt.Rows.Count

        Dim sheetName As String = ""
        Dim ws As Object
        Dim chart As Object

        chart = Me.GetCurrentWorkBook.Charts.Add(, Me.GetCurrentWorkBook.Sheets.Item(Me.GetCurrentWorkBook.Sheets.Count))
        chart.ChartType = XlChartType.xlLineMarkers

        '开始添加系列
        For index As Integer = 1 To Me.GetCurrentWorkBook.Sheets.Count - 1
            Try

                ws = Me.GetCurrentWorkBook.Sheets.Item(index)
                sheetName = ws.Name

                chart.SeriesCollection.NewSeries()

            Catch ex As Exception

            End Try
        Next

        '开始设置
        Dim realIndex As Integer = 1

        For index As Integer = 1 To Me.GetCurrentWorkBook.Sheets.Count
            Try

                ws = Me.GetCurrentWorkBook.Sheets.Item(index)
                sheetName = ws.Name

                If index = 1 Then
                    chart.SeriesCollection(realIndex).XValues = "=" & sheetName & "!R" & 开始行 & "C" & 标签列 & ":R" & 结束行 & "C" & 标签列
                    chart.SeriesCollection(realIndex).Values = "=" & sheetName & "!R" & 开始行 & "C" & 值列 & ":R" & 结束行 & "C" & 值列
                    chart.SeriesCollection(realIndex).Name = "=""" & sheetName & """"
                Else
                    chart.SeriesCollection(realIndex).Values = "=" & sheetName & "!R" & 开始行 & "C" & 值列 & ":R" & 结束行 & "C" & 值列
                    chart.SeriesCollection(realIndex).Name = "=""" & sheetName & """"
                End If
                realIndex = realIndex + 1
            Catch ex As Exception

            End Try
        Next

        '设置图例
        chart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, False, False, False, False, False, True, False, False, False)

        chart.Location(XlChartLocation.xlLocationAsNewSheet, "节余趋势图")
        chart.Move(, Me.GetCurrentWorkBook.Sheets(Me.GetCurrentWorkBook.Sheets.Count))

        chart.HasLegend = True
        chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom

    End Sub

    Public Sub 特别保存()
        If _bClose = False Then
            RaiseEvent PrintProcess("打印结束")
            保存()
            RaiseEvent PrintProcess("正在关闭......")
            关闭()
            _bClose = True
            RaiseEvent PrintProcess("")
        End If
    End Sub

    Private Sub SetTemp(ByVal page As Integer)
        CurrentSheetIndex = 1
        With GetCurrentWorkSheet.Range("A1:" & _endColumnName & 报表记录条数字)
            .Copy()
        End With

        CurrentSheetIndex = _backCurrentIndex
        With Me.GetCurrentWorkSheet
            '.Range("A" & CStr((page - 1) * pageCount + 1)).Select()
            With .Range("A" & CStr((page - 1) * 报表记录条数字 + 1))
                '复制格式
                .PasteSpecial(XlPasteType.xlPasteFormats, XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)
                '复制列宽
                .PasteSpecial(XlPasteType.xlPasteColumnWidths, XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)
            End With                    '
        End With

    End Sub

    Private Sub SetTemp()

        '只设置格式 不要设置单元格 内容
        With Me.GetCurrentWorkSheet
            '设置列宽
            .Columns.Item("A:A", Type.Missing).ColumnWidth = 15
            .Columns.Item("B:B", Type.Missing).ColumnWidth = 18

            .Columns.Item("C:C", Type.Missing).ColumnWidth = 18
            .Columns.Item("D:D", Type.Missing).ColumnWidth = 18

            '设置标题背景边框
            With .Range("A1:" & _endColumnName & "1")
                .Interior.ColorIndex = 15
                With .Font
                    .Bold = True
                    .Name = 报表字体
                    .Size = 12
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = -4142
                End With
            End With

            '设置边框
            With .Range("A1:" & _endColumnName & 报表记录条数字)
                With .Font
                    .Name = 报表字体
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
            End With

            With .Range("B2:" & _endColumnName & 报表记录条数字)
                .NumberFormatLocal = "0.00_ "
            End With

        End With
    End Sub

    Private Sub PrintContent(ByVal dt As System.Data.DataTable)
        Dim page As Integer = Fix(dt.Rows.Count / (报表记录条数字 - 1))
        If dt.Rows.Count Mod (报表记录条数字 - 1) <> 0 Then
            page += 1
        End If

        Dim sb As New System.Text.StringBuilder
        For index As Integer = 1 To page
            Dim startRow As Integer = (index - 1) * (报表记录条数字 - 1)

            sb.AppendLine("统计日期" & vbTab & "收入金额" & vbTab & "支出金额" & vbTab & "合计")
            For jdex As Integer = 1 To 报表记录条数字 - 1
                startRow += 1
                If startRow > dt.Rows.Count Then
                    Exit For
                End If

                sb.Append(CStr(dt.Rows(startRow - 1)("月份")).Trim)
                sb.Append(vbTab)

                sb.Append(CStr(dt.Rows(startRow - 1)("收入")).Trim)
                sb.Append(vbTab)

                sb.Append(CStr(dt.Rows(startRow - 1)("支出")).Trim)
                sb.Append(vbTab)

                sb.AppendLine(CStr(dt.Rows(startRow - 1)("统计")).Trim)
            Next
        Next

        Dim con As String = sb.ToString.Trim
        If con.Length > 0 Then
            With Me.GetCurrentWorkSheet
                Clipboard.SetDataObject(con)
                .Range("A1").Select()
                .Paste()
            End With
        End If
    End Sub

#Region "报表必须使用"
    Private _报表字体 As String = "宋体"
    Public Overrides Property 报表字体() As String
        Get
            Return _报表字体
        End Get
        Set(ByVal value As String)
            _报表字体 = value
        End Set
    End Property

    Private _报表记录条数字 As Integer = 53
    Public Overrides Property 报表记录条数字() As Integer
        Get
            Return _报表记录条数字
        End Get
        Set(ByVal value As Integer)
            _报表记录条数字 = value
        End Set
    End Property
#End Region
End Class
